package server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import server.vo.TbCardVO;


public class TbCardDAO {

	public boolean insertCardBill(Connection conn, TbCardVO tbCardVO) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
					
			StringBuffer sql = new StringBuffer();
			
			sql.append("\n INSERT INTO dbo.TB_CARD_BILL                                 " );
			sql.append("\n (bill_code, user_id, bill_date, bill_amount, shop_name, shop_location, state, process_date, type )    " );
			sql.append("\n VALUES                                                          " );
			sql.append("\n (?,?,convert(datetime, ?, 20),?,?,?,?,convert(datetime, ?, 20),?)                                                     " );
			
			pstmt = conn.prepareStatement(sql.toString());
						
			pstmt.setString(1, tbCardVO.getBill_code());
			pstmt.setString(2, tbCardVO.getUser_id());
			pstmt.setString(3, tbCardVO.getBill_date());
			pstmt.setLong(4, tbCardVO.getBill_amount());
			pstmt.setString(5, tbCardVO.getShop_name());
			pstmt.setString(6, tbCardVO.getShop_location());
			pstmt.setString(7, tbCardVO.getState()); 
			pstmt.setString(8, tbCardVO.getProcess_date());
			pstmt.setString(9, tbCardVO.getType());
			
			return pstmt.executeUpdate() > 0 ? true : false;

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (rs != null)
				rs.close();

			if (pstmt != null)
				pstmt.close();
		}
	}
	
	public boolean updateCardBill(Connection conn, TbCardVO tbCardVO) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {		
			StringBuffer sql = new StringBuffer();
			sql.append("\n UPDATE dbo.TB_CARD_BILL                               " );
			sql.append("\n SET state = ?                                        " );
			sql.append("\n , process_date = convert(datetime, ?, 20)                                          " );
			sql.append("\n WHERE bill_code = ?                                   " );
	
			pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.setString(1, tbCardVO.getState());
			pstmt.setString(2, tbCardVO.getProcess_date());
			pstmt.setString(3, tbCardVO.getBill_code());

			return pstmt.executeUpdate() > 0 ? true : false;

		} catch (Exception e) {
			return false;
		} finally {
			if (rs != null)
				rs.close();

			if (pstmt != null)
				pstmt.close();
		}
	}
	
	public ArrayList<TbCardVO> inqrCardBillList(Connection conn, TbCardVO tbCardVO) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {		
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT bill_code                                         " );
			sql.append("\n        , user_id                                         " );
			sql.append("\n        , bill_date                                         " );
			sql.append("\n        , bill_amount                                         " );
			sql.append("\n        , shop_name                                         " );
			sql.append("\n        , shop_location                                         " );
			sql.append("\n        , state                                         " );
			sql.append("\n        , process_date                                         " );
			sql.append("\n        , type                                         " );
			
			sql.append("\n  FROM dbo.TB_CARD_BILL                                 " );
						
			
			
			//primary key case
			if(tbCardVO.getBill_code() != null && !tbCardVO.getBill_code().isEmpty()) {
				sql.append("\n WHERE bill_code = ?                              " );
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setString(1, tbCardVO.getBill_code());
			}
			else { //no primary key case
				sql.append("\n WHERE user_id = ? ");
				
				if(tbCardVO.getState() != null && !tbCardVO.getState().isEmpty()) {
					sql.append("\n and state = ? ");
				}
				
				if(tbCardVO.getType() != null && !tbCardVO.getType().isEmpty()) {
					sql.append("\n and type = ? ");
				}
				
				pstmt = conn.prepareStatement(sql.toString());
				int idx = 1 ;
				pstmt.setString(idx++, tbCardVO.getUser_id()); // user_id
				
				if(tbCardVO.getState() != null && !tbCardVO.getState().isEmpty()) {
					pstmt.setString(idx++, tbCardVO.getState()); // Y / N					
				}
				if(tbCardVO.getType() != null && !tbCardVO.getType().isEmpty()) {
					pstmt.setString(idx++, tbCardVO.getType()); // S  / M
				}
				
				
			}						
			rs = pstmt.executeQuery();
			ArrayList<TbCardVO> results = new ArrayList<TbCardVO>();
			while (rs.next()) {
				TbCardVO entry = new TbCardVO();				
				entry.setBill_code(rs.getString("bill_code"));
				entry.setUser_id(rs.getString("user_id"));
				entry.setBill_date(rs.getString("bill_date"));
				entry.setBill_amount(rs.getLong("bill_amount"));
				entry.setShop_name(rs.getString("shop_name"));
				entry.setShop_location(rs.getString("shop_location"));
				entry.setState(rs.getString("state"));
				entry.setProcess_date(rs.getString("process_date"));
				entry.setType(rs.getString("type"));
				results.add(entry);
			}
			return results;

		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null)
				rs.close();

			if (pstmt != null)
				pstmt.close();
		}
	}
	
	public String inqrMaxCardBillCode(Connection conn) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		
		String today = "";
		
		try {		
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT isnull(MAX(BILL_CODE), CONVERT(nvarchar(20), getdate(), 112)+'0000') AS bill_code " );
			sql.append("\n   FROM dbo.TB_CARD_BILL a                                                                  " );
			sql.append("\n  WHERE LEFT(BILL_CODE, 8) = convert(nvarchar(8), getdate(), 112)                                                              " );
	
			pstmt = conn.prepareStatement(sql.toString());
			//pstmt.setString(1, today);
			rs = pstmt.executeQuery();
			
			String maxpCode = "";
			if (rs.next()){
				long pcode= Long.parseLong(rs.getString("bill_code"));
				pcode = pcode+1;
				maxpCode = String.valueOf(pcode);
			}

			return maxpCode;

		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null)
				rs.close();

			if (pstmt != null)
				pstmt.close();
		}
	}
}
